﻿using Npgsql;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CreateEntity.DataBase
{
    /// <summary>
    /// pgsql
    /// </summary>
    public class PostgreSql:IDB
    {
        public List<Table> GetTableAll(DbConnection conn)
        {
            List<Table> tables = new List<Table>();

            NpgsqlCommand com = new NpgsqlCommand($@"
                                    select tb.table_name, d.description
                                    from information_schema.tables tb
                                    join pg_class c on c.relname = tb.table_name
                                    left join pg_description d on d.objoid = c.oid and d.objsubid = '0'
                                    where tb.table_schema='public' 
                                    ", (NpgsqlConnection)conn);
            using NpgsqlDataReader reder = com.ExecuteReader();
            while (reder.Read())
            {
                tables.Add(new Table
                {
                    Name = reder["table_name"].ToString(),
                    Description = reder["description"].ToString()
                });
            }

            tables.ForEach(i =>
            {
                i.CSharpName = Helper.upString(i.Name);
            });

            return tables;
        }

        public List<TableColumn> GetTableColumn(DbConnection conn, Table table)
        {
            NpgsqlCommand com = new NpgsqlCommand($@"
                                    select 
	                                    l.table_name,
	                                    (case when 
		                                    (select count(*) from pg_constraint 
			                                    where conrelid = a.attrelid and conkey[1]=attnum and contype='p'
		                                    )>0  
		                                    then 'p' 
		                                    else null end
	                                    ) as constraint_type,
	                                    a.attname,
	                                    concat_ws('',t.typname,substring(format_type(a.atttypid,a.atttypmod) from '.∗')) as type,
	                                    d.description,
	                                    l.is_nullable,
	                                    l.character_maximum_length 
                                    from 
	                                    information_schema.columns l,
	                                    pg_class c, 
	                                    pg_attribute a , 
	                                    pg_type t, 
	                                    pg_description d
                                    where 
	                                    c.relname = '{table.Name}'
	                                    and l.table_name = '{table.Name}'
	                                    and a.attnum>0 
	                                    and a.attname = l.column_name
	                                    and a.attrelid = c.oid 
	                                    and a.atttypid = t.oid 
	                                    and d.objoid=a.attrelid 
	                                    and d.objsubid=a.attnum
                                    ", (NpgsqlConnection)conn);
            using NpgsqlDataReader reder = com.ExecuteReader();

            List<TableColumn> tableColumn = new List<TableColumn>();

            while (reder.Read())
            {
                tableColumn.Add(new TableColumn
                {
                    TableName = reder["table_name"].ToString(),
                    ColumnName = reder["attname"].ToString(),
                    DataType = reder["type"].ToString(),
                    DataLength = reder["character_maximum_length"].ToString(),
                    Nullable = reder["is_nullable"].ToString(),
                    Comments = reder["description"].ToString(),
                    ConstraintType = reder["constraint_type"].ToString()
                });
            }

            tableColumn.ForEach(i =>
            {
                i.CSharpName = Helper.upString(i.ColumnName);
            });
            return tableColumn;
        }
    }
}
